package com.gss.stockLedger.service;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.ss.format.CellTextFormatter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.gss.stockLedger.dao.QueryVendorWiseDAO;
import com.gss.stockLedger.domain.FIFOReportDTO;
import com.gss.stockLedger.domain.StockTransactionDtls;
import com.gss.stockLedger.form.VendorWiseReportFilter;
import com.gss.stockLedger.util.ApplicationConstants;

@Service
public class VendorWiseReportGenerationService {

	@Autowired
	QueryVendorWiseDAO queryVendorWiseDAO;
	
	public boolean generateVendorWiseReport(VendorWiseReportFilter vendorWiseReportFilter, Date date) throws IOException {
		
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd-MMM-yyyy");
		SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("ddMMyyyyHHmmss");

		boolean resultOfSuccess = false;
		
		try{
			List<Object[]> vendorCodeList = null;
			if(vendorWiseReportFilter.getVendorCode().equalsIgnoreCase("All")){
				vendorCodeList = queryVendorWiseDAO.getVendorList(vendorWiseReportFilter.getCompanyName(),vendorWiseReportFilter.getVendorCode());
				System.out.println("Size of vendor code list: " + vendorCodeList.size());
			}
			else if(vendorWiseReportFilter.getVendorCode().equalsIgnoreCase("Spec")){
				vendorCodeList = queryVendorWiseDAO.getVendorList(vendorWiseReportFilter.getCompanyName(),vendorWiseReportFilter.getSpecVendorCode());
				System.out.println("Size of vendor code list: " + vendorCodeList.size());
			}
			
			
			
			List<FIFOReportDTO> finalReportList = null;
			String sheetName = ApplicationConstants.vendorWiseSheetName;
			Sheet sheet = null;
			Workbook workbook = ExcelCreationService.createExcelFile(sheetName,"",vendorWiseReportFilter.getStartDate(),vendorWiseReportFilter.getEndDate(),date, "", "", "",vendorWiseReportFilter.getCompanyName());
			
			Integer excelRowCounter = 10;
			Integer lastRowCounter = 0;
			FIFOReportDTO fifoReportDTO = null;
			
			Long grandTotalQuantity = (long) 0;
			Float grandTotalRate = (float) 0.0;
			Double grandTotalValue = 0.0;
	
			Long subTotalQuantity = (long) 0;
			Float subTotalRate = (float) 0.0;
			Double subTotalValue = 0.0;
	
			sheet = workbook.getSheet(ApplicationConstants.vendorWiseSheetName);
			System.out.println("################################# START #################################");
			
			for(Object[] vendorCode : vendorCodeList){
	
				
				if(vendorCode[0] == null){
					System.out.println("Vendor Code is null");
					continue;
				}
				System.out.println("Processing for vendor Id: " + vendorCode[0].toString());
				finalReportList = new ArrayList<FIFOReportDTO>();
					
				subTotalQuantity = (long) 0;
				subTotalRate = (float) 0.0;
				subTotalValue = 0.0;
				
				
				List<StockTransactionDtls> stockPurchaseDtls = queryVendorWiseDAO.getVendorWisePurchaseReport(vendorWiseReportFilter.getCompanyName()
						,vendorWiseReportFilter.getEndDate(),vendorCode[0].toString());
				
				if(stockPurchaseDtls.size() == 0){
					continue;
				}
				//System.out.println("Size Of StockList: " + stockPurchaseDtls.size());				
				
				Row vendorInfoRow = sheet.createRow(excelRowCounter-1);
				sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 5));
				Cell cell0 = vendorInfoRow.createCell(0);
				if(null != vendorCode)
					cell0.setCellValue("Vendor Code: "+" "+ vendorCode[0].toString() + " / " +"VendorName: "+ vendorCode[1].toString());
				
				for (StockTransactionDtls stockTransactionDtls2 : stockPurchaseDtls) {
										
					fifoReportDTO = new FIFOReportDTO();
					if(null != stockTransactionDtls2.getInventoryType()){
						fifoReportDTO.setInventoryType(stockTransactionDtls2.getInventoryType());
					}
					if(null != stockTransactionDtls2.getPartId()){
						fifoReportDTO.setPartId(stockTransactionDtls2.getPartId());
					}
					if(null != stockTransactionDtls2.getPartDesc()){
						fifoReportDTO.setPartDesc(stockTransactionDtls2.getPartDesc());
					}
					if(null != stockTransactionDtls2.getSapDocNo()){
						fifoReportDTO.setSapDocNo(stockTransactionDtls2.getSapDocNo());
					}
					if(null != stockTransactionDtls2.getSapEntryDate()){
						fifoReportDTO.setSapEntryDate(stockTransactionDtls2.getSapEntryDate());
					}
					if(null != stockTransactionDtls2.getAssignmentId()){
						fifoReportDTO.setAssignmentId(stockTransactionDtls2.getAssignmentId());
					}
					if(null != stockTransactionDtls2.getTransactionDate()){
						fifoReportDTO.setTransactionDate(stockTransactionDtls2.getTransactionDate());
					}
					if(null != stockTransactionDtls2.getInvoiceNo()){
						fifoReportDTO.setInvoiceNo(stockTransactionDtls2.getInvoiceNo());
					}
					if(null != stockTransactionDtls2.getStrInvoiceDate()){
						fifoReportDTO.setStrInvoiceDate(stockTransactionDtls2.getStrInvoiceDate());
					}
					if(null != stockTransactionDtls2.getValueFCY()){
						fifoReportDTO.setValueFCY(stockTransactionDtls2.getValueFCY());
					}
					if(null != stockTransactionDtls2.getUnitFCY()){
						fifoReportDTO.setSapDocNo(stockTransactionDtls2.getUnitFCY());
					}
					if(null != stockTransactionDtls2.getPurchaseType()){
						fifoReportDTO.setPurchaseType(stockTransactionDtls2.getPurchaseType());
					}
					if(null != stockTransactionDtls2.getTransactionType()){
						fifoReportDTO.setTransactionType(stockTransactionDtls2.getTransactionType());
					}
					if(null != stockTransactionDtls2.getAcctType()){
						fifoReportDTO.setAcctType(stockTransactionDtls2.getAcctType());
					}
					if(null != stockTransactionDtls2.getQuantity()){
						fifoReportDTO.setQuantity(stockTransactionDtls2.getQuantity());
						subTotalQuantity+=stockTransactionDtls2.getQuantity();
					}
					if(null != stockTransactionDtls2.getRate()){
						fifoReportDTO.setRate(stockTransactionDtls2.getRate());
						subTotalRate+=stockTransactionDtls2.getRate();
					}
					if(null != stockTransactionDtls2.getValue()){
						fifoReportDTO.setValue(stockTransactionDtls2.getValue());
						subTotalValue+=stockTransactionDtls2.getValue();
					}
					if(null != stockTransactionDtls2.getVendorCode()){
						fifoReportDTO.setVendorCode(stockTransactionDtls2.getVendorCode());
					}
					if(null != stockTransactionDtls2.getVendorName()){
						fifoReportDTO.setVendorName(stockTransactionDtls2.getVendorName());
					}
					
					finalReportList.add(fifoReportDTO);
					//System.out.println("Size of final report list: " + finalReportList.size());
				}
				System.out.println("---------------------------------- ExcelRowCounter: " + excelRowCounter);
				excelRowCounter = ExcelCreationService.appendVendorWiseSheet(excelRowCounter, workbook, finalReportList, vendorWiseReportFilter, date);
				
				CellStyle cs1 = workbook.createCellStyle();
				cs1.setFillForegroundColor(IndexedColors.AQUA.getIndex());
				cs1.setFillPattern(CellStyle.SOLID_FOREGROUND);
				
				Row subTotalRow = sheet.createRow(excelRowCounter);
				Cell cellsT00 = subTotalRow.createCell(0);
				Cell cellsT12 = subTotalRow.createCell(12);
				Cell cellsT14 = subTotalRow.createCell(14);
				Cell cellsT15 = subTotalRow.createCell(15);
				Cell cellsT16 = subTotalRow.createCell(16);
				
				sheet.addMergedRegion(new CellRangeAddress(excelRowCounter, excelRowCounter, 0, 11));
				sheet.addMergedRegion(new CellRangeAddress(excelRowCounter, excelRowCounter, 12, 13));
				
				cellsT12.setCellValue("Sub Total: ");
				cellsT14.setCellValue(subTotalQuantity);
				cellsT15.setCellValue(subTotalRate);
				cellsT16.setCellValue(subTotalValue);
				
				cellsT00.setCellStyle(cs1);
				cellsT12.setCellStyle(cs1);
				cellsT14.setCellStyle(cs1);
				cellsT15.setCellStyle(cs1);
				cellsT16.setCellStyle(cs1);
				
				grandTotalQuantity+=subTotalQuantity;
				grandTotalRate+=subTotalRate;
				grandTotalValue+=subTotalValue;
				
				excelRowCounter+=3;
				lastRowCounter = excelRowCounter;
				
				resultOfSuccess = true;
			}
			
			
				CellStyle cs2 = workbook.createCellStyle();
				cs2.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
				cs2.setFillPattern(CellStyle.SOLID_FOREGROUND);
			
				Row grandTotalRow = sheet.createRow(lastRowCounter+2);
				Cell cellgT00 = grandTotalRow.createCell(0);
				Cell cellgT12 = grandTotalRow.createCell(12);
				Cell cellgT14 = grandTotalRow.createCell(14);
				Cell cellgT15 = grandTotalRow.createCell(15);
				Cell cellgT16 = grandTotalRow.createCell(16);
				
				sheet.addMergedRegion(new CellRangeAddress(lastRowCounter+2, lastRowCounter+2, 0, 11));
				sheet.addMergedRegion(new CellRangeAddress(lastRowCounter+2, lastRowCounter+2, 12, 13));
				
				cellgT12.setCellValue("GrandTotal Total: ");
				cellgT14.setCellValue(grandTotalQuantity);
				cellgT15.setCellValue(grandTotalRate);
				cellgT16.setCellValue(grandTotalValue);
				
				cellgT00.setCellStyle(cs2);
				cellgT12.setCellStyle(cs2);
				cellgT14.setCellStyle(cs2);
				cellgT15.setCellStyle(cs2);
				cellgT16.setCellStyle(cs2);
				
				String pathName = ApplicationConstants.pathToExcel+ System.getProperty("file.separator") + ApplicationConstants.vendorWiseSheetName
						+ System.getProperty("file.separator") + simpleDateFormat.format(date) + System.getProperty("file.separator");
				
				String fileName = simpleDateFormat.format(vendorWiseReportFilter.getStartDate()) 
						+ simpleDateFormat.format(vendorWiseReportFilter.getEndDate()) + simpleDateFormat2.format(date) + ".xls";
				
				
				FileOutputStream fileOut = new FileOutputStream(pathName + fileName);
				workbook.write(fileOut);
				fileOut.close();
				System.out.println("################################# END #################################");
		}
		catch (Throwable e) {
			e.printStackTrace();
		}
		
		return resultOfSuccess;
	}

}
